const { pool } = require('../config/db');
const { downloadImage } = require('../utils/fileUtils');
const config = require('../config/config');
const path = require('path');
const fs = require('fs');

/**
 * 头像修复脚本
 * 用于修复已处理数据中的头像问题：
 * 1. avatar_path 为 null
 * 2. 文件不存在
 * 3. 文件大小为 0
 */
class AvatarFixer {
  /**
   * 检查文件是否存在且有效
   * @param {string} filePath - 文件路径
   * @returns {boolean} - 是否有效
   */
  isValidFile(filePath) {
    if (!filePath) {
      return false;
    }

    try {
      // 构建完整的文件路径
      const fullPath = path.resolve(filePath);
      
      // 检查文件是否存在
      if (!fs.existsSync(fullPath)) {
        return false;
      }

      // 检查文件大小
      const stats = fs.statSync(fullPath);
      if (stats.size === 0) {
        return false;
      }

      return true;
    } catch (error) {
      console.error(`检查文件失败: ${filePath}`, error.message);
      return false;
    }
  }

  /**
   * 诊断头像问题类型
   * @param {string} filePath - 文件路径
   * @returns {string} - 问题描述
   */
  diagnoseIssue(filePath) {
    if (!filePath) {
      return 'avatar_path为null';
    }

    try {
      const fullPath = path.resolve(filePath);
      
      if (!fs.existsSync(fullPath)) {
        return 'avatar_path有值但文件不存在';
      }

      const stats = fs.statSync(fullPath);
      if (stats.size === 0) {
        return 'avatar_path有值但文件大小为0';
      }

      return '文件有效';
    } catch (error) {
      return `检查失败: ${error.message}`;
    }
  }

  /**
   * 删除无效的头像文件
   * @param {string} filePath - 文件路径
   */
  deleteInvalidFile(filePath) {
    try {
      if (filePath && fs.existsSync(path.resolve(filePath))) {
        fs.unlinkSync(path.resolve(filePath));
        console.log(`  - 已删除无效文件: ${filePath}`);
      }
    } catch (error) {
      console.error(`  - 删除文件失败: ${filePath}`, error.message);
    }
  }

  /**
   * 获取所有需要修复的记录
   * @returns {Promise<Array>} - 需要修复的记录列表
   */
  async getRecordsToFix() {
    try {
      console.log('\n正在查询需要修复的记录...');

      const sql = `
        SELECT 
          pp.id,
          pp.raw_personnel_id,
          pp.chinese_name,
          pp.english_name,
          pp.avatar_path,
          rp.avatar_url
        FROM processed_personnel pp
        LEFT JOIN raw_personnel rp ON pp.raw_personnel_id = rp.id
        WHERE rp.avatar_url IS NOT NULL 
          AND rp.avatar_url != ''
        ORDER BY pp.id
      `;

      const [rows] = await pool.execute(sql);
      
      // 过滤出需要修复的记录
      const needsFix = [];
      for (const row of rows) {
        const needsFixing = !this.isValidFile(row.avatar_path);
        if (needsFixing) {
          needsFix.push(row);
        }
      }

      console.log(`✓ 找到 ${needsFix.length} 条需要修复的记录（共 ${rows.length} 条有头像URL的记录）\n`);
      
      return needsFix;
    } catch (error) {
      console.error('查询需要修复的记录失败:', error.message);
      throw error;
    }
  }

  /**
   * 修复单条记录的头像
   * @param {Object} record - 记录信息
   * @returns {Promise<Object>} - 修复结果
   */
  async fixSingleRecord(record) {
    try {
      const name = record.chinese_name || record.english_name || 'Unknown';
      const issueType = this.diagnoseIssue(record.avatar_path);
      
      console.log(`[${record.id}] ${name}`);
      console.log(`  问题类型: ${issueType}`);
      console.log(`  原头像路径: ${record.avatar_path || '(无)'}`);
      console.log(`  头像URL: ${record.avatar_url}`);

      // 删除旧的无效文件（如果文件存在）
      if (record.avatar_path) {
        this.deleteInvalidFile(record.avatar_path);
      }

      // 下载新头像
      console.log(`  正在下载头像...`);
      const newAvatarPath = await downloadImage(
        record.avatar_url,
        path.resolve(config.upload.avatarDir)
      );

      if (!newAvatarPath) {
        console.log(`  ✗ 下载失败\n`);
        return {
          success: false,
          id: record.id,
          name: name,
          error: '下载失败'
        };
      }

      // 验证新文件
      if (!this.isValidFile(newAvatarPath)) {
        console.log(`  ✗ 下载的文件无效\n`);
        this.deleteInvalidFile(newAvatarPath);
        return {
          success: false,
          id: record.id,
          name: name,
          error: '下载的文件无效'
        };
      }

      // 更新数据库
      const updateSql = `
        UPDATE processed_personnel 
        SET avatar_path = ?, updated_at = NOW()
        WHERE id = ?
      `;
      await pool.execute(updateSql, [newAvatarPath, record.id]);

      console.log(`  ✓ 修复成功: ${newAvatarPath}\n`);

      return {
        success: true,
        id: record.id,
        name: name,
        oldPath: record.avatar_path,
        newPath: newAvatarPath
      };
    } catch (error) {
      console.error(`  ✗ 修复失败: ${error.message}\n`);
      return {
        success: false,
        id: record.id,
        name: record.chinese_name || record.english_name || 'Unknown',
        error: error.message
      };
    }
  }

  /**
   * 批量修复头像
   * @param {number} batchSize - 每批处理数量（默认不限制，一次性处理所有）
   * @returns {Promise<Object>} - 修复结果
   */
  async fixAll(batchSize = null) {
    try {
      console.log('\n========== 开始修复头像 ==========');
      console.log(`时间: ${new Date().toLocaleString()}`);
      console.log('===================================\n');

      // 获取需要修复的记录
      const records = await this.getRecordsToFix();

      if (records.length === 0) {
        console.log('✓ 没有需要修复的记录');
        console.log('\n===================================\n');
        return {
          success: true,
          message: '没有需要修复的记录',
          totalCount: 0,
          successCount: 0,
          failCount: 0
        };
      }

      console.log(`准备修复 ${records.length} 条记录...\n`);

      // 限制批次大小（如果指定）
      const recordsToProcess = batchSize 
        ? records.slice(0, batchSize) 
        : records;

      // 逐条修复
      const results = [];
      for (let i = 0; i < recordsToProcess.length; i++) {
        console.log(`[${i + 1}/${recordsToProcess.length}] `);
        const result = await this.fixSingleRecord(recordsToProcess[i]);
        results.push(result);

        // 添加短暂延迟，避免请求过快
        if (i < recordsToProcess.length - 1) {
          await new Promise(resolve => setTimeout(resolve, 500));
        }
      }

      // 统计结果
      const successCount = results.filter(r => r.success).length;
      const failCount = results.filter(r => !r.success).length;

      console.log('\n========== 修复完成 ==========');
      console.log(`总数: ${recordsToProcess.length}`);
      console.log(`成功: ${successCount}`);
      console.log(`失败: ${failCount}`);
      
      if (batchSize && records.length > batchSize) {
        console.log(`\n提示: 还有 ${records.length - batchSize} 条记录未处理`);
        console.log(`可以再次运行此脚本继续处理`);
      }
      
      console.log('===============================\n');

      // 打印失败记录详情
      if (failCount > 0) {
        console.log('失败记录详情:');
        results.filter(r => !r.success).forEach(r => {
          console.log(`  - [${r.id}] ${r.name}: ${r.error}`);
        });
        console.log('');
      }

      return {
        success: true,
        totalCount: recordsToProcess.length,
        successCount,
        failCount,
        remainingCount: batchSize ? Math.max(0, records.length - batchSize) : 0,
        details: results
      };
    } catch (error) {
      console.error('修复头像失败:', error.message);
      throw error;
    }
  }

  /**
   * 获取头像统计信息
   * @param {boolean} silent - 是否静默模式（不打印日志）
   * @returns {Promise<Object>} - 统计信息
   */
  async getStatistics(silent = false) {
    try {
      if (!silent) {
        console.log('\n========== 头像统计信息 ==========\n');
      }

      // 查询所有已处理的记录
      const [allRecords] = await pool.execute(`
        SELECT 
          pp.id,
          pp.avatar_path,
          rp.avatar_url
        FROM processed_personnel pp
        LEFT JOIN raw_personnel rp ON pp.raw_personnel_id = rp.id
      `);

      let totalRecords = allRecords.length;
      let hasUrlCount = 0;        // 有原始URL的数量
      let hasPathCount = 0;       // 有avatar_path的数量
      let validFileCount = 0;     // 文件存在且有效的数量
      let nullPathCount = 0;      // avatar_path为null的数量
      let fileNotExistCount = 0;  // 文件不存在的数量
      let zeroSizeCount = 0;      // 文件大小为0的数量

      for (const record of allRecords) {
        // 统计有URL的记录
        if (record.avatar_url) {
          hasUrlCount++;
        }

        // 统计有路径的记录
        if (record.avatar_path) {
          hasPathCount++;

          // 检查文件是否有效
          const fullPath = path.resolve(record.avatar_path);
          if (!fs.existsSync(fullPath)) {
            fileNotExistCount++;
          } else {
            const stats = fs.statSync(fullPath);
            if (stats.size === 0) {
              zeroSizeCount++;
            } else {
              validFileCount++;
            }
          }
        } else {
          nullPathCount++;
        }
      }

      const needsFixCount = hasUrlCount - validFileCount;

      if (!silent) {
        console.log(`总记录数: ${totalRecords}`);
        console.log(`有原始URL: ${hasUrlCount}`);
        console.log(`有avatar_path: ${hasPathCount}`);
        console.log(`\n文件状态:`);
        console.log(`  ✓ 有效文件: ${validFileCount}`);
        console.log(`  ✗ avatar_path为null: ${nullPathCount}`);
        console.log(`  ✗ avatar_path有值但文件不存在: ${fileNotExistCount}`);
        console.log(`  ✗ avatar_path有值但文件大小为0: ${zeroSizeCount}`);
        console.log(`\n需要修复: ${needsFixCount} 条`);
        
        if (needsFixCount > 0) {
          console.log('\n问题分类:');
          if (nullPathCount > 0) {
            console.log(`  - ${nullPathCount} 条记录的avatar_path为null`);
          }
          if (fileNotExistCount > 0) {
            console.log(`  - ${fileNotExistCount} 条记录的avatar_path有值但文件不存在`);
          }
          if (zeroSizeCount > 0) {
            console.log(`  - ${zeroSizeCount} 条记录的文件大小为0`);
          }
        }
        
        console.log('\n================================\n');
      }

      return {
        totalRecords,
        hasUrlCount,
        hasPathCount,
        validFileCount,
        nullPathCount,
        fileNotExistCount,
        zeroSizeCount,
        needsFixCount
      };
    } catch (error) {
      console.error('获取统计信息失败:', error.message);
      throw error;
    }
  }

  /**
   * 详细诊断头像问题（列出具体的问题记录）
   * @param {number} limit - 限制显示数量
   * @returns {Promise<Object>} - 诊断结果
   */
  async diagnoseIssues(limit = 20) {
    try {
      console.log('\n========== 头像问题诊断 ==========\n');

      // 查询所有有URL但头像有问题的记录
      const sql = `
        SELECT 
          pp.id,
          pp.chinese_name,
          pp.english_name,
          pp.avatar_path,
          rp.avatar_url
        FROM processed_personnel pp
        LEFT JOIN raw_personnel rp ON pp.raw_personnel_id = rp.id
        WHERE rp.avatar_url IS NOT NULL 
          AND rp.avatar_url != ''
        ORDER BY pp.id
      `;

      const [rows] = await pool.execute(sql);
      
      // 分类统计问题
      const issues = {
        nullPath: [],
        fileNotExist: [],
        zeroSize: []
      };

      for (const row of rows) {
        const diagnosis = this.diagnoseIssue(row.avatar_path);
        
        if (diagnosis === 'avatar_path为null') {
          issues.nullPath.push(row);
        } else if (diagnosis === 'avatar_path有值但文件不存在') {
          issues.fileNotExist.push(row);
        } else if (diagnosis === 'avatar_path有值但文件大小为0') {
          issues.zeroSize.push(row);
        }
      }

      const totalIssues = issues.nullPath.length + issues.fileNotExist.length + issues.zeroSize.length;

      console.log(`问题总数: ${totalIssues} 条\n`);

      // 显示 avatar_path 为 null 的记录
      if (issues.nullPath.length > 0) {
        console.log(`【问题1】avatar_path为null: ${issues.nullPath.length} 条`);
        const showCount = Math.min(limit, issues.nullPath.length);
        for (let i = 0; i < showCount; i++) {
          const record = issues.nullPath[i];
          const name = record.chinese_name || record.english_name || 'Unknown';
          console.log(`  [${record.id}] ${name}`);
        }
        if (issues.nullPath.length > limit) {
          console.log(`  ... 还有 ${issues.nullPath.length - limit} 条记录`);
        }
        console.log('');
      }

      // 显示文件不存在的记录
      if (issues.fileNotExist.length > 0) {
        console.log(`【问题2】avatar_path有值但文件不存在: ${issues.fileNotExist.length} 条`);
        const showCount = Math.min(limit, issues.fileNotExist.length);
        for (let i = 0; i < showCount; i++) {
          const record = issues.fileNotExist[i];
          const name = record.chinese_name || record.english_name || 'Unknown';
          console.log(`  [${record.id}] ${name}`);
          console.log(`       路径: ${record.avatar_path}`);
        }
        if (issues.fileNotExist.length > limit) {
          console.log(`  ... 还有 ${issues.fileNotExist.length - limit} 条记录`);
        }
        console.log('');
      }

      // 显示文件大小为0的记录
      if (issues.zeroSize.length > 0) {
        console.log(`【问题3】avatar_path有值但文件大小为0: ${issues.zeroSize.length} 条`);
        const showCount = Math.min(limit, issues.zeroSize.length);
        for (let i = 0; i < showCount; i++) {
          const record = issues.zeroSize[i];
          const name = record.chinese_name || record.english_name || 'Unknown';
          console.log(`  [${record.id}] ${name}`);
          console.log(`       路径: ${record.avatar_path}`);
        }
        if (issues.zeroSize.length > limit) {
          console.log(`  ... 还有 ${issues.zeroSize.length - limit} 条记录`);
        }
        console.log('');
      }

      if (totalIssues === 0) {
        console.log('✓ 所有头像文件都正常！\n');
      }

      console.log('================================\n');

      return {
        totalIssues,
        nullPathCount: issues.nullPath.length,
        fileNotExistCount: issues.fileNotExist.length,
        zeroSizeCount: issues.zeroSize.length,
        nullPathRecords: issues.nullPath,
        fileNotExistRecords: issues.fileNotExist,
        zeroSizeRecords: issues.zeroSize
      };
    } catch (error) {
      console.error('诊断失败:', error.message);
      throw error;
    }
  }
}

// 如果直接运行此脚本
if (require.main === module) {
  const fixer = new AvatarFixer();
  
  // 解析命令行参数
  const args = process.argv.slice(2);
  const command = args[0] || 'stats';
  
  (async () => {
    try {
      if (command === 'stats' || command === 'statistics') {
        // 查看统计信息
        await fixer.getStatistics();
      } else if (command === 'diagnose' || command === 'diag') {
        // 详细诊断问题
        const limit = args[1] ? parseInt(args[1]) : 20;
        await fixer.diagnoseIssues(limit);
      } else if (command === 'fix') {
        // 修复头像
        const batchSize = args[1] ? parseInt(args[1]) : null;
        await fixer.fixAll(batchSize);
      } else if (command === 'help') {
        // 显示帮助信息
        console.log('\n头像修复工具使用说明:');
        console.log('====================\n');
        console.log('查看统计信息:');
        console.log('  node src/scripts/fixAvatars.js stats');
        console.log('');
        console.log('详细诊断问题（列出具体记录）:');
        console.log('  node src/scripts/fixAvatars.js diagnose');
        console.log('  node src/scripts/fixAvatars.js diagnose 10  # 限制显示10条');
        console.log('');
        console.log('修复所有头像:');
        console.log('  node src/scripts/fixAvatars.js fix');
        console.log('');
        console.log('修复指定数量的头像:');
        console.log('  node src/scripts/fixAvatars.js fix 10');
        console.log('');
        console.log('显示帮助:');
        console.log('  node src/scripts/fixAvatars.js help');
        console.log('');
      } else {
        console.log('未知命令。使用 "help" 查看帮助信息。');
      }
      
      process.exit(0);
    } catch (error) {
      console.error('执行失败:', error);
      process.exit(1);
    }
  })();
}

module.exports = AvatarFixer;

